<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Locking and Indexes</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.1.2 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Concurrency Control"
HREF="mvcc.html"><LINK
REL="PREVIOUS"
TITLE="Data Consistency Checks at the Application Level"
HREF="applevel-consistency.html"><LINK
REL="NEXT"
TITLE="Performance Tips"
HREF="performance-tips.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2011-12-01T22:07:59"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.1.2 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Data Consistency Checks at the Application Level"
HREF="applevel-consistency.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="mvcc.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 13. Concurrency Control</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Performance Tips"
HREF="performance-tips.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="LOCKING-INDEXES"
>13.5. Locking and Indexes</A
></H1
><P
>    Though <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>
    provides nonblocking read/write access to table
    data, nonblocking read/write access is not currently offered for every
    index access method implemented
    in <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>.
    The various index types are handled as follows:

    <P
></P
></P><DIV
CLASS="VARIABLELIST"
><DL
><DT
>B-tree and <ACRONYM
CLASS="ACRONYM"
>GiST</ACRONYM
> indexes</DT
><DD
><P
>        Short-term share/exclusive page-level locks are used for
        read/write access. Locks are released immediately after each
        index row is fetched or inserted.  These index types provide
        the highest concurrency without deadlock conditions.
       </P
></DD
><DT
>Hash indexes</DT
><DD
><P
>        Share/exclusive hash-bucket-level locks are used for read/write
        access.  Locks are released after the whole bucket is processed.
        Bucket-level locks provide better concurrency than index-level
        ones, but deadlock is possible since the locks are held longer
        than one index operation.
       </P
></DD
><DT
><ACRONYM
CLASS="ACRONYM"
>GIN</ACRONYM
> indexes</DT
><DD
><P
>        Short-term share/exclusive page-level locks are used for
        read/write access. Locks are released immediately after each
        index row is fetched or inserted. But note that insertion of a
        GIN-indexed value usually produces several index key insertions
        per row, so GIN might do substantial work for a single value's
        insertion.
       </P
></DD
></DL
></DIV
><P>
   </P
><P
>    Currently, B-tree indexes offer the best performance for concurrent
    applications; since they also have more features than hash
    indexes, they are the recommended index type for concurrent
    applications that need to index scalar data. When dealing with
    non-scalar data, B-trees are not useful, and GiST or GIN indexes should
    be used instead.
   </P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="applevel-consistency.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="performance-tips.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Data Consistency Checks at the Application Level</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="mvcc.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Performance Tips</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>